BEGIN;
CREATE TABLE "IUStoreApp_customer" (
    "id" serial NOT NULL PRIMARY KEY,
    "customer_name" varchar(32) NOT NULL,
    "customer_gender" varchar(2) NOT NULL
)
;
CREATE TABLE "IUStoreApp_member" (
    "customer_ptr_id" integer NOT NULL PRIMARY KEY REFERENCES "IUStoreApp_customer" ("id") DEFERRABLE INITIALLY DEFERRED,
    "member_phone" varchar(12) NOT NULL,
    "member_address" varchar(50) NOT NULL,
    "member_email" varchar(32) NOT NULL,
    "member_join_date" date NOT NULL,
    "member_other_detail" varchar(100) NOT NULL
)
;
CREATE TABLE "IUStoreApp_guest" (
    "customer_ptr_id" integer NOT NULL PRIMARY KEY REFERENCES "IUStoreApp_customer" ("id") DEFERRABLE INITIALLY DEFERRED,
    "guest_subscription_email" varchar(32) NOT NULL
)
;
CREATE TABLE "IUStoreApp_supplier" (
    "id" serial NOT NULL PRIMARY KEY,
    "supplier_address" varchar(50) NOT NULL,
    "supplier_name" varchar(32) NOT NULL,
    "supplier_phone" varchar(12) NOT NULL,
    "supplier_email" varchar(32) NOT NULL,
    "supplier_other_detail" varchar(100) NOT NULL
)
;
CREATE TABLE "IUStoreApp_product" (
    "id" serial NOT NULL PRIMARY KEY,
    "supplier_id" integer NOT NULL REFERENCES "IUStoreApp_supplier" ("id") DEFERRABLE INITIALLY DEFERRED,
    "product_name" varchar(32) NOT NULL,
    "product_type" varchar(3) NOT NULL,
    "product_price" double precision NOT NULL,
    "product_description" varchar(100) NOT NULL
)
;
CREATE TABLE "IUStoreApp_promotionproduct" (
    "id" serial NOT NULL PRIMARY KEY,
    "product_id" integer NOT NULL UNIQUE REFERENCES "IUStoreApp_product" ("id") DEFERRABLE INITIALLY DEFERRED,
    "promotion_rate" double precision NOT NULL
)
;
CREATE TABLE "IUStoreApp_inventory" (
    "id" serial NOT NULL PRIMARY KEY,
    "product_id" integer NOT NULL UNIQUE REFERENCES "IUStoreApp_product" ("id") DEFERRABLE INITIALLY DEFERRED,
    "inv_product_quantity" integer NOT NULL
)
;
CREATE TABLE "IUStoreApp_order" (
    "id" serial NOT NULL PRIMARY KEY,
    "customer_id" integer NOT NULL REFERENCES "IUStoreApp_customer" ("id") DEFERRABLE INITIALLY DEFERRED,
    "order_status" varchar(2) NOT NULL,
    "order_date" date NOT NULL,
    "order_deliver_address" varchar(50) NOT NULL,
    "order_detail" varchar(200) NOT NULL
)
;
CREATE TABLE "IUStoreApp_ordereditem" (
    "id" serial NOT NULL PRIMARY KEY,
    "order_id" integer NOT NULL REFERENCES "IUStoreApp_order" ("id") DEFERRABLE INITIALLY DEFERRED,
    "product_id" integer NOT NULL REFERENCES "IUStoreApp_product" ("id") DEFERRABLE INITIALLY DEFERRED,
    "quantity" integer NOT NULL
)
;
CREATE TABLE "IUStoreApp_employee" (
    "id" serial NOT NULL PRIMARY KEY,
    "employee_address" varchar(50) NOT NULL,
    "employee_name" varchar(32) NOT NULL,
    "employee_phone" varchar(12) NOT NULL,
    "employee_email" varchar(32) NOT NULL,
    "employee_other_detail" varchar(100) NOT NULL
)
;
CREATE TABLE "IUStoreApp_manager" (
    "employee_ptr_id" integer NOT NULL PRIMARY KEY REFERENCES "IUStoreApp_employee" ("id") DEFERRABLE INITIALLY DEFERRED,
    "year_of_experience" double precision NOT NULL
)
;
CREATE TABLE "IUStoreApp_clerk" (
    "employee_ptr_id" integer NOT NULL PRIMARY KEY REFERENCES "IUStoreApp_employee" ("id") DEFERRABLE INITIALLY DEFERRED,
    "manager_id" integer NOT NULL REFERENCES "IUStoreApp_manager" ("employee_ptr_id") DEFERRABLE INITIALLY DEFERRED,
    "clerk_employment_type" varchar(2) NOT NULL,
    "clerk_description" varchar(100) NOT NULL
)
;
CREATE TABLE "IUStoreApp_servedby" (
    "id" serial NOT NULL PRIMARY KEY,
    "customer_id" integer NOT NULL UNIQUE REFERENCES "IUStoreApp_customer" ("id") DEFERRABLE INITIALLY DEFERRED,
    "clerk_id" integer NOT NULL REFERENCES "IUStoreApp_clerk" ("employee_ptr_id") DEFERRABLE INITIALLY DEFERRED,
    "served_time" date NOT NULL
)
;
COMMIT;
